package com.sxfq.loansupermarket.backsystem.modular.marketing.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.DateUtil;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.PayServiceInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.TongJiInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.system.transfer.UserDto;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.AddVisitedInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @author hzp
 * @Date
 */
@Controller
@RequestMapping("/reservationsArrive")
public class ReservationsArrivedController extends BaseController {

    @Autowired
    IConsultantService service;

    @Autowired
    IReservationsService reservationsService;

    private String PREFIX = "/marketing/reservations/";

    @Autowired
    private SqlMapper sqlMapper;


    /**
     * 已到 点击姓名跳转客户管理详情页面  （需要获取到成交记录   和  看诊记录的数据）
     */
    @RequestMapping(value = "/reservations_arrive/{id}")
    public String to_reservationsNotDetail(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql1 = "select table_name from information_schema.tables where table_schema='crms'\n" +
                "and table_name like '%customer_info%'";
        List<ReservationsInfo> list1 = sqlMapper.selectList(sql1, ReservationsInfo.class);
        String tableHead1 = list1.get(0).getTableName().substring(0, list1.get(0).getTableName().length() - 13);
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " group_concat(distinct t1.doctor) as doctor," +
                "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                "(\n" +
                "        CASE t3.customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "            '男'\n" +
                "        WHEN 2 THEN\n" +
                "            '女'\n" +
                "        ELSE\n" +
                "            '未知'\n" +
                "        END\n" +
                "    ) as sex  ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(0).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                " where t1.reservations_id='" + id + "' and pay_service_id is not null  and t1.deal_status ='已成交' " +
                " group by  operationTime ";
        for (int i = 1; i < list1.size(); i++) {
            tableHead1 = list1.get(i).getTableName().substring(0, list1.get(i).getTableName().length() - 13);
            sql += " union all select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                    "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                    "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                    "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                    "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                    " group_concat(distinct t1.doctor) as doctor," +
                    "left(t1.operation_time,19) as operationTime,t1.folding_pay as foldingPay," +
                    "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                    "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                    "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                    "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                    "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                    "(\n" +
                    "        CASE t3.customer_sex\n" +
                    "        WHEN 1 THEN\n" +
                    "            '男'\n" +
                    "        WHEN 2 THEN\n" +
                    "            '女'\n" +
                    "        ELSE\n" +
                    "            '未知'\n" +
                    "        END\n" +
                    "    ) as sex  ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                    " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(i).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                    " where t1.reservations_id='" + id + "' and pay_service_id is not null and t1.deal_status ='已成交' " +
                    " group by  operationTime ";
        }
        sql += " order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
        /*Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;*/
        String getTime = "";
        for (PayServiceInfo info : payServiceInfoList) {
            if (info != null) {
               if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                /* if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }*/
                getTime = info.getGetTime();
            }
        }
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(id);

        for (ConsultantInfo infos : consultantInfoList) {
            if (infos.getDiagnosisTime() != "" && infos.getDiagnosisTime() != null) {
                infos.setDiagnosisTimeOne(infos.getDiagnosisTime().substring(0, 10));
                infos.setDiagnosisTimeTwo(infos.getDiagnosisTime().substring(11, infos.getDiagnosisTime().length()));
            }
        }
        String tcName = "";
        String firstVisitDate = "";
//        String sql2 = "select t2.id as customerId,t2.reservations_id as reservationsId,t2.customer_name as customerName,t2.medical_record_number as medicalRecordNumber" +
//                ",t2.customer_sex as customerSex,t2.customer_age as customerAge ,t2.field_tc as tcName" +
//                " from " + this.getTableName() + " t2 where reservations_id='" + id + "' order by t2.arrival_time desc limit 0,1";
//        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sql3 = "select id, tc_name as tcName,first_visit_date as firstVisitDate from reservations_info a where id= '" + id + "' limit 0,1";
        ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
        if (reservationsInfo1 != null) {
            tcName = reservationsInfo1.getTcName();
            firstVisitDate = reservationsInfo1.getFirstVisitDate();
        }

        //成交记录数据返回模型视图
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ReservationsInfo reservationsInfo = this.reservationsService.selectOneById(id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("reservationsId", id);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("getTime", getTime);


        List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(id);
        model.addAttribute("revisitRecordList", revisitRecordList);
//        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("tcName", tcName);
        model.addAttribute("firstVisitDate", firstVisitDate);

        return PREFIX + "reservationsReach.html";
    }

    /**
     * 已到 点击姓名跳转客户管理详情页面  （需要获取到成交记录   和  看诊记录的数据）
     */
    @RequestMapping(value = "/reservations_arrives/{id}")
    public String to_reservationsNotDetails(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql1 = "select table_name from information_schema.tables where table_schema='crms'\n" +
                "and table_name like '%customer_info%'";
        List<ReservationsInfo> list1 = sqlMapper.selectList(sql1, ReservationsInfo.class);
        String tableHead1 = list1.get(0).getTableName().substring(0, list1.get(0).getTableName().length() - 13);
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " group_concat(distinct t1.doctor) as doctor," +
                "left(t1.operation_time,19) as operationTime,t1.folding_pay as foldingPay," +
                "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                "(\n" +
                "        CASE t3.customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "            '男'\n" +
                "        WHEN 2 THEN\n" +
                "            '女'\n" +
                "        ELSE\n" +
                "            '未知'\n" +
                "        END\n" +
                "    ) as sex ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(0).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                " where t1.reservations_id='" + id + "' and pay_service_id is not null  and t1.deal_status ='已成交' " +
                " group by  operationTime ";
        for (int i = 1; i < list1.size(); i++) {
            tableHead1 = list1.get(i).getTableName().substring(0, list1.get(i).getTableName().length() - 13);
            sql += " union all select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                    "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                    "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                    "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                    "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                    " group_concat(distinct t1.doctor) as doctor," +
                    "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                    "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                    "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                    "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                    "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                    "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                    "(\n" +
                    "        CASE t3.customer_sex\n" +
                    "        WHEN 1 THEN\n" +
                    "            '男'\n" +
                    "        WHEN 2 THEN\n" +
                    "            '女'\n" +
                    "        ELSE\n" +
                    "            '未知'\n" +
                    "        END\n" +
                    "    ) as sex ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                    " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(i).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                    " where t1.reservations_id='" + id + "' and pay_service_id is not null and t1.deal_status ='已成交' " +
                    " group by  operationTime ";
        }
        sql += " order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
        /*Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;*/
        String getTime = "";
        for (PayServiceInfo info : payServiceInfoList) {
            if (info != null) {
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                /*if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }*/
                getTime = info.getGetTime();
            }
        }
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(id);

        for (ConsultantInfo infos : consultantInfoList) {
            if (infos.getDiagnosisTime() != "" && infos.getDiagnosisTime() != null) {
                infos.setDiagnosisTimeOne(infos.getDiagnosisTime().substring(0, 10));
                infos.setDiagnosisTimeTwo(infos.getDiagnosisTime().substring(11, infos.getDiagnosisTime().length()));
            }
        }
        String tcName = "";
        String firstVisitDate = "";
//        String sql2 = "select t2.id as customerId,t2.reservations_id as reservationsId,t2.customer_name as customerName,t2.medical_record_number as medicalRecordNumber" +
//                ",t2.customer_sex as customerSex,t2.customer_age as customerAge ,t2.field_tc as tcName" +
//                " from " + this.getTableName() + " t2 where reservations_id='" + id + "' order by t2.arrival_time desc limit 0,1";
//        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sql3 = "select id, tc_name as tcName,first_visit_date as firstVisitDate from reservations_info a where id= '" + id + "' limit 0,1";
        ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
        if (reservationsInfo1 != null) {
            tcName = reservationsInfo1.getTcName();
            firstVisitDate = reservationsInfo1.getFirstVisitDate();
        }

        //成交记录数据返回模型视图
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ReservationsInfo reservationsInfo = this.reservationsService.selectOneById(id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("reservationsId", id);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("getTime", getTime);


        List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(id);
        model.addAttribute("revisitRecordList", revisitRecordList);
//        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("tcName", tcName);
        model.addAttribute("firstVisitDate", firstVisitDate);

        return PREFIX + "reservationsReachs.html";
    }

    /**
     * 已到 点击姓名跳转客户管理详情页面  （需要获取到成交记录   和  看诊记录的数据）
     */
    @RequestMapping(value = "/reservations_arrivesr/{id}")
    public String reservations_arrivesr(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql1 = "select table_name from information_schema.tables where table_schema='crms'\n" +
                "and table_name like '%customer_info%'";
        List<ReservationsInfo> list1 = sqlMapper.selectList(sql1, ReservationsInfo.class);
        String tableHead1 = list1.get(0).getTableName().substring(0, list1.get(0).getTableName().length() - 13);
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " group_concat(distinct t1.doctor) as doctor," +
                "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                "(\n" +
                "        CASE t3.customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "            '男'\n" +
                "        WHEN 2 THEN\n" +
                "            '女'\n" +
                "        ELSE\n" +
                "            '未知'\n" +
                "        END\n" +
                "    ) as sex ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(0).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                " where t1.reservations_id='" + id + "' and pay_service_id is not null  and t1.deal_status ='已成交' " +
                " group by  operationTime ";
        for (int i = 1; i < list1.size(); i++) {
            tableHead1 = list1.get(i).getTableName().substring(0, list1.get(i).getTableName().length() - 13);
            sql += " union all select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                    "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                    "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                    "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                    "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                    " group_concat(distinct t1.doctor) as doctor," +
                    "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                    "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                    "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                    "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                    "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                    "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                    "(\n" +
                    "        CASE t3.customer_sex\n" +
                    "        WHEN 1 THEN\n" +
                    "            '男'\n" +
                    "        WHEN 2 THEN\n" +
                    "            '女'\n" +
                    "        ELSE\n" +
                    "            '未知'\n" +
                    "        END\n" +
                    "    ) as sex ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                    " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(i).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                    " where t1.reservations_id='" + id + "' and pay_service_id is not null and t1.deal_status ='已成交' " +
                    " group by  operationTime ";
        }
        sql += " order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
       /* Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;*/
        String getTime = "";
        for (PayServiceInfo info : payServiceInfoList) {
            if (info != null) {
               if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                /* if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }*/
                getTime = info.getGetTime();
            }
        }
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(id);

        for (ConsultantInfo infos : consultantInfoList) {
            if (infos.getDiagnosisTime() != "" && infos.getDiagnosisTime() != null) {
                infos.setDiagnosisTimeOne(infos.getDiagnosisTime().substring(0, 10));
                infos.setDiagnosisTimeTwo(infos.getDiagnosisTime().substring(11, infos.getDiagnosisTime().length()));
            }
        }
        String tcName = "";
        String firstVisitDate = "";
//        String sql2 = "select t2.id as customerId,t2.reservations_id as reservationsId,t2.customer_name as customerName,t2.medical_record_number as medicalRecordNumber" +
//                ",t2.customer_sex as customerSex,t2.customer_age as customerAge ,t2.field_tc as tcName" +
//                " from " + this.getTableName() + " t2 where reservations_id='" + id + "' order by t2.arrival_time desc limit 0,1";
//        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sql3 = "select id, tc_name as tcName,first_visit_date as firstVisitDate from reservations_info a where id= '" + id + "' limit 0,1";
        ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
        if (reservationsInfo1 != null) {
            tcName = reservationsInfo1.getTcName();
            firstVisitDate = reservationsInfo1.getFirstVisitDate();
        }

        //成交记录数据返回模型视图
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ReservationsInfo reservationsInfo = this.reservationsService.selectOneById(id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("reservationsId", id);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("getTime", getTime);


        List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(id);
        model.addAttribute("revisitRecordList", revisitRecordList);
//        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("tcName", tcName);
        model.addAttribute("firstVisitDate", firstVisitDate);

        return PREFIX + "reservationsReachsr.html";
    }

    /**
     * 已到 点击姓名跳转客户管理详情页面  （需要获取到成交记录   和  看诊记录的数据）
     */
    @RequestMapping(value = "/reservations_arrivess/{id}")
    public String to_reservationsNotDetailss(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = ShiroKit.getUser().getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql1 = "select table_name from information_schema.tables where table_schema='crms'\n" +
                "and table_name like '%customer_info%'";
        List<ReservationsInfo> list1 = sqlMapper.selectList(sql1, ReservationsInfo.class);
        String tableHead1 = list1.get(0).getTableName().substring(0, list1.get(0).getTableName().length() - 13);
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " group_concat(distinct t1.doctor) as doctor," +
                "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                "   (" +
                "       CASE t3.customer_sex" +
                "       WHEN 1 THEN" +
                "       '男'" +
                "       WHEN 2 THEN" +
                "       '女'" +
                "       ELSE" +
                "       '未知'" +
                "       END" +
                "   ) as sex ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(0).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                " where t1.reservations_id='" + id + "' and pay_service_id is not null  and t1.deal_status ='已成交' " +
                " group by  operationTime ";
        for (int i = 1; i < list1.size(); i++) {
            tableHead1 = list1.get(i).getTableName().substring(0, list1.get(i).getTableName().length() - 13);
            sql += " union all select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ," +
                    "t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ," +
                    "t1.pay_service_num as payServiceNum , t1.service_actual_pay as serviceActualPay ," +
                    "sum(t1.service_actual_pay) as totleServiceActualPay,sum(t1.folding_pay) as totleFoldingPay," +
                    "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                    " group_concat(distinct t1.doctor) as doctor," +
                    "left(t1.operation_time,19) as operationTime, t1.folding_pay as foldingPay," +
                    "t1.to_void_cause as toVoidCause,t1.pay_service as payService,t2.id as payServiceId," +
                    "t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                    "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, " +
                    "t2.pay_service_price_two as payServicePriceTwo,t1.to_void_cause as toVoidCause ,t1.no_deal_cause as noDealCause,\n" +
                    "t3.customer_name as customerName,t3.reservations_id as reservationsId,t3.medical_record_number as medicalRecordNumber," +
                    "   (" +
                    "       CASE t3.customer_sex" +
                    "       WHEN 1 THEN" +
                    "       '男'" +
                    "       WHEN 2 THEN" +
                    "       '女'" +
                    "       ELSE" +
                    "       '未知'" +
                    "       END" +
                    "   ) as sex ,t3.customer_age as customerAge,t3.customer_phone as customerPhone ,t3.field_tc as fieldTc \n" +
                    " ,t1.get_time as getTime from " + tableHead1 + "deal_info t1 left join pay_service_info t2 on t1.pay_service_id=t2.id left join " + list1.get(i).getTableName() + " t3 on t1.customer_id=t3.id\n" +
                    " where t1.reservations_id='" + id + "' and pay_service_id is not null and t1.deal_status ='已成交' " +
                    " group by  operationTime ";
        }
        sql += " order by operationTime desc";
        List<PayServiceInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServiceInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        Double accountArrears = 0.00;
        String getTime = "";
        for (PayServiceInfo info : payServiceInfoList) {
            if (info != null) {
               if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
               /* if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }*/
                getTime = info.getGetTime();
            }
        }
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList(id);

        for (ConsultantInfo infos : consultantInfoList) {
            if (infos.getDiagnosisTime() != "" && infos.getDiagnosisTime() != null) {
                infos.setDiagnosisTimeOne(infos.getDiagnosisTime().substring(0, 10));
                infos.setDiagnosisTimeTwo(infos.getDiagnosisTime().substring(11, infos.getDiagnosisTime().length()));
            }
        }
        String tcName = "";
        String firstVisitDate = "";
//        String sql2 = "select t2.id as customerId,t2.reservations_id as reservationsId,t2.customer_name as customerName,t2.medical_record_number as medicalRecordNumber" +
//                ",t2.customer_sex as customerSex,t2.customer_age as customerAge ,t2.field_tc as tcName" +
//                " from " + this.getTableName() + " t2 where reservations_id='" + id + "' order by t2.arrival_time desc limit 0,1";
//        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql2, ConsultantInfo.class);
        String sql3 = "select id, tc_name as tcName,first_visit_date as firstVisitDate from reservations_info a where id= '" + id + "' limit 0,1";
        ReservationsInfo reservationsInfo1 = sqlMapper.selectOne(sql3, ReservationsInfo.class);
        if (reservationsInfo1 != null) {
            tcName = reservationsInfo1.getTcName();
            firstVisitDate = reservationsInfo1.getFirstVisitDate();
        }

        //成交记录数据返回模型视图
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        ReservationsInfo reservationsInfo = this.reservationsService.selectOneById(id);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("reservationsId", id);
        model.addAttribute("reservationsInfo", reservationsInfo);
        model.addAttribute("getTime", getTime);


        List<AddVisitedInfo> revisitRecordList = service.getRevisitRecordList(id);
        model.addAttribute("revisitRecordList", revisitRecordList);
//        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("consultantInfoList", consultantInfoList);
        model.addAttribute("tcName", tcName);
        model.addAttribute("firstVisitDate", firstVisitDate);

        return PREFIX + "reservationsReachss.html";
    }

    @RequestMapping("/account")
    @ResponseBody
    public HashMap sumAmount(@RequestParam(required = false) String reach,
                             @RequestParam(required = false) String appointmentPayService,
                             @RequestParam(required = false) String archiving,
                             @RequestParam(required = false) String accessDisease,
                             @RequestParam(required = false) String startFirstVisitDate,
                             @RequestParam(required = false) String endFirstVisitDate,
                             @RequestParam(required = false) String outpatientName,
                             @RequestParam(required = false) String oneChannel, @RequestParam(required = false) String status,
                             @RequestParam(required = false) String twoChannel, @RequestParam(required = false) String thrChannel,
                             @RequestParam(required = false) String statuss,
                             @RequestParam(required = false) String department, HttpServletResponse response) {

        String sql = "select sum(t2.money) as totalMoney " +
                " from reservations_info t  left join  reservations_aggregate_info t2 on t.id=t2.reservations_id " +
                "where t.status ='到店'";
        if (StringUtil.isNotEmpty(reach)) {
            sql += " and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') or t.wechat_number like CONCAT('%','" + reach + "','%') or t.reservations_num like CONCAT('%','" + reach + "','%'))  ";
        }
        if (StringUtil.isNotEmpty(appointmentPayService)) {
            sql += " and t.appointment_pay_service = '" + appointmentPayService + "'";
        }
        if (StringUtil.isNotEmpty(archiving)) {
            sql += " and t.archiving = '" + archiving + "'";
        }
        if (StringUtil.isNotEmpty(accessDisease)) {
            sql += " and t.access_disease = '" + accessDisease + "'";
        }
        if (StringUtil.isNotEmpty(startFirstVisitDate)) {
            sql += " and t.first_visit_date  >= '" + startFirstVisitDate + "'";
        }
        if (StringUtil.isNotEmpty(endFirstVisitDate)) {
            sql += " and t.first_visit_date  <= '" + endFirstVisitDate + "'";
        }
        if (StringUtil.isNotEmpty(outpatientName)) {
            sql += " and t.outpatient_name  = '" + outpatientName + "'";
        }
        if (StringUtil.isNotEmpty(oneChannel) && !oneChannel.equals("请选择")) {
            sql += " and t.one_channel  = '" + oneChannel + "'";
        }
        if (StringUtil.isNotEmpty(twoChannel) && !twoChannel.equals("请选择")) {
            sql += " and t.two_channel  = '" + twoChannel + "'";
        }
        if (StringUtil.isNotEmpty(thrChannel) && !thrChannel.equals("请选择")) {
            sql += " and t.thr_channel  = '" + thrChannel + "'";
        }
        if (StringUtil.isNotEmpty(outpatientName)) {
            sql += " and t.outpatient_name  = '" + outpatientName + "'";
        }
        if (StringUtil.isNotEmpty(department)) {
            sql += " and t.department  = '" + department + "'";
        }
        if (StringUtil.isNotEmpty(status)) {
            sql += " and t.status  = '" + status + "'";
        }
        Map<String, Object> result = sqlMapper.selectOne(sql.toString());
        HashMap map = new HashMap();
        if (result != null) {
            map.put("totalMoney", result.get("totalMoney"));
        } else {
            map.put("totalMoney", "0");
        }
        return map;
    }

    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }


}